package io.renren.data;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import io.renren.RenrenApplication;
import io.renren.modules.avanotes.dao.SubtitleDetailsDao;
import io.renren.modules.avanotes.dao.VocabularyDao;
import io.renren.modules.avanotes.entity.SubtitleDetailsEntity;
import io.renren.modules.avanotes.entity.VocabularyEntity;
import io.renren.modules.avanotes.service.VocabularyService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @author: xiaomi
 * @date: 2022/1/31
 * @description:
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RenrenApplication.class)
@Slf4j
public class VocabularyImport {

    @Autowired
    SubtitleDetailsDao sdDao;


    @Autowired
    VocabularyDao vDao;

    @Autowired
    VocabularyService vService;

    /**
     * 将文件导入 DB
     *
     * @throws Exception
     */
    @Test
    public void importEOC() throws Exception {
        List<VocabularyEntity> ll = new ArrayList<>();

        String path = "E:\\File\\Vocabulary\\ECONOMIST高频词\\ECO高频词汇表 - 副本.xlsx";
        List<List<String>> data = redExcel(path);
        for (List<String> datum : data) {
            if (datum.size() != 2) {
                log.info("发现不合规的数据！");
                continue;
            }
//            if ("IPO".equals(datum.get(0))) {
//                VocabularyEntity entity = new VocabularyEntity(datum.get(0), datum.get(1));
//                ll.add(entity);
//            }
            VocabularyEntity entity = new VocabularyEntity(datum.get(0), datum.get(1));
            ll.add(entity);
        }
        //批量添加
        vService.saveBatch(ll);
    }


    /**
     * 将文件做标记
     */
    @Test
    public void matchMovie() {
        Set<String> englishSet = new HashSet<>();
        //1.数据预先处理
        List<SubtitleDetailsEntity> detailsList = sdDao.selectList(new QueryWrapper<>());
        //将句中所有的单词分割出来
        for (SubtitleDetailsEntity detailsEntity : detailsList) {
            String english = detailsEntity.getEnglish();
            String[] strings = english.split("[\\s,.?!]");
            for (String string : strings) {
                englishSet.add(string.toLowerCase());
            }
        }

        //2.进行标记
        int count = 0;
        List<VocabularyEntity> vocabularyList = vDao.selectList(new QueryWrapper<>());
        for (VocabularyEntity vocabularyEntity : vocabularyList) {
            if (englishSet.contains(vocabularyEntity.getWord().toLowerCase())) {
                vDao.updateMatchById(vocabularyEntity.getId());
            } else {
                count++;
            }
        }
        //
        log.info("未命中数量:{}", count);
    }


    //region private methods

    /**
     * 读取excel内容
     * <p>
     * 用户模式下：
     * 弊端：对于少量的数据可以，单数对于大量的数据，会造成内存占据过大，有时候会造成内存溢出
     * 建议修改成事件模式
     */
    public static List<List<String>> redExcel(String filePath) throws Exception {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        //ArrayList<Map<String, String>> list = new ArrayList<>();
        List<List<String>> list = new ArrayList<>();

        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            //LinkedHashMap<String, String> map = new LinkedHashMap<>();
            List<String> ll = new ArrayList<>(3);
            // 读取每一格内容
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);
                // cell.setCellType(XSSFCell.CELL_TYPE_STRING); 过期，使用下面替换
                cell.setCellType(CellType.STRING);
                if (cell.getStringCellValue().equals("")) {
                    continue;
                }
                //map.put(getString(titleCell), getString(cell));
                ll.add(getString(cell));
            }
            if (ll.isEmpty()) {
                continue;
            }
            list.add(ll);
        }
        return list;
    }

    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }
    //endregion
}
